package com.example.mapp.buildSql;

import com.example.entity.training.TrainingPlan;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

public class TrainingPlanMappSql {
    /**
     * 查询培训计划
     * @param trainingPlan
     * @return
     */
    public String queryTrainingPlan(TrainingPlan trainingPlan){
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" training_plan where 1=1 ");
        if(trainingPlan.getId() != null){
            sb.append(" and id = #{id}" );
        }
        if(StringUtils.isNotEmpty(trainingPlan.getPlanName())){
            String planName = "'%"+trainingPlan.getPlanName()+"%'";
            sb.append(" and plan_name like "+planName);
        }
        if(StringUtils.isNotEmpty(trainingPlan.getPlanContent())){
            String planContent = "'%"+trainingPlan.getPlanContent()+"%'";
            sb.append(" and plan_content like "+planContent);
        }
        if(StringUtils.isNotEmpty(trainingPlan.getLeadUnitId())){
            sb.append(" and lead_unit_id = #{leadUnitId}");
        }
        if(StringUtils.isNotEmpty(trainingPlan.getPlanType())){
            sb.append(" and plan_type = #{planType}");
        }
        if(StringUtils.isNotEmpty(trainingPlan.getStartDate())){
            sb.append(" and start_date >= #{startDate}");
        }
        if(StringUtils.isNotEmpty(trainingPlan.getEndDate())){
            sb.append(" and end_date <= #{endDate}");
        }
        sb.append(" order by id desc ");
        return sb.toString();
    }
    /**
     * 获取培训对象
     * @param trainingPlan
     * @return
     */
    public String queryPartyByTwo(TrainingPlan trainingPlan){

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DISTINCT ");
        sb.append(" p.id party_id, ");
        sb.append(" p.party_name, ");
        sb.append(" sur.user_id, ");
        sb.append(" su.user_name ");
        sb.append(" FROM party p ");
        sb.append(" LEFT JOIN party_user pu ON pu.party_id = p.id ");
        sb.append(" LEFT JOIN sys_user_role sur ON pu.user_id = sur.user_id ");
        sb.append(" LEFT JOIN sys_user su ON su.id = sur.user_id ");
        sb.append(" WHERE 1 = 1  ");

        String planType = trainingPlan.getPlanType();
        if("1".equals(planType)){
            sb.append(" and p.party_type = '1' and sur.role_id='17' ");
        }
        if("2".equals(planType)){
            sb.append(" and p.party_type = '2' and sur.role_id='16' ");
        }
        if("4".equals(planType)){
            sb.append(" and p.party_type = '2' and sur.role_id='18' ");
        }
        String leadUnitId = trainingPlan.getLeadUnitId();
        if(leadUnitId != null && !"".equals(leadUnitId)){
            sb.append(" and p.id != #{leadUnitId} ");
            sb.append(" AND p.id in ( ");
            sb.append("     select id from ( ");
            sb.append("         select DISTINCT used_party_id as id from elevator r where r.branch_office in (select py.id from party py where py.parent_id=#{leadUnitId} and party_type='4') and r.used_party_id is not null ");
            sb.append("         UNION ");
            sb.append("         select DISTINCT maintenance_party_id as id from elevator r where r.branch_office in (select py.id from party py where py.parent_id=#{leadUnitId} and party_type='4') and r.maintenance_party_id is not null ");
            sb.append(" ) a )");
        }
        sb.append(" and sur.user_id is not null ");
        return sb.toString();
    }

    /**
     * 获取培训对象 分区
     * @param trainingPlan
     * @return
     */
    public String queryPartyByThree(TrainingPlan trainingPlan){

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DISTINCT ");
        sb.append(" p.id party_id, ");
        sb.append(" p.party_name, ");
        sb.append(" sur.user_id, ");
        sb.append(" su.user_name ");
        sb.append(" FROM party p ");
        sb.append(" LEFT JOIN party_user pu ON pu.party_id = p.id ");
        sb.append(" LEFT JOIN sys_user_role sur ON pu.user_id = sur.user_id ");
        sb.append(" LEFT JOIN sys_user su ON su.id = sur.user_id ");
        sb.append(" WHERE 1 = 1  ");

        String planType = trainingPlan.getPlanType();
        if("1".equals(planType)){
            sb.append(" and p.party_type = '1' and sur.role_id='17' ");
        }
        if("2".equals(planType)){
            sb.append(" and p.party_type = '2' and sur.role_id='16' ");
        }
        if("4".equals(planType)){
            sb.append(" and p.party_type = '2' and sur.role_id='18' ");
        }
        String leadUnitId = trainingPlan.getLeadUnitId();
        if(leadUnitId != null && !"".equals(leadUnitId)){
            sb.append(" and p.id != #{leadUnitId} ");
            sb.append(" AND p.id in ( ");
            sb.append("     select id from ( ");
            sb.append("         select DISTINCT used_party_id as id from elevator r where r.branch_office =#{leadUnitId} and r.used_party_id is not null ");
            sb.append("         UNION ");
            sb.append("         select DISTINCT maintenance_party_id as id from elevator r where r.branch_office =#{leadUnitId} and r.maintenance_party_id is not null ");
            sb.append(" ) a )");
        }
        sb.append(" and sur.user_id is not null ");
        return sb.toString();
    }

    /**
     * 获取培训对象
     * @param trainingPlan
     * @return
     */
    public String queryUnitAndUser(TrainingPlan trainingPlan){
        String select = " DISTINCT p.id party_id,p.party_name,sur.user_id,su.user_name ";

        String table = " sub_party_info p  " + "LEFT JOIN party_user pu ON pu.party_id = p.id   "
                + "LEFT JOIN sys_user_role sur ON pu.user_id = sur.user_id "
                + "LEFT JOIN system_role sr ON sur.role_id = sr.id  "
                + "LEFT JOIN sys_user su on su.id = sur.user_id ";

        String whereParam = " and sr.role_name like '%管理人员%' ";
        String planType = trainingPlan.getPlanType();
        if(planType != null && !"".equals(planType)){
            whereParam += " and p.party_type = #{planType}";
        }
        String leadUnitId = trainingPlan.getLeadUnitId();
        if(leadUnitId != null && !"".equals(leadUnitId)){
            whereParam += " and p.id != #{leadUnitId} ";
            whereParam += " and p.parent_id=#{leadUnitId} ";
        }
        String where = " 1=1 " + whereParam;
        String sql = new SQL() {
            {
                SELECT(select);
                FROM(table);
                WHERE(where);
            }
        }.toString();
        return sql;
    }
}
